/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOManufacture;
import DTO.DTOProduct;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALProduct {
    ConnectionDB conn;
    
    public DALProduct() 
    {
        conn = new ConnectionDB();
    }
    
    //get all product
    public ArrayList<DTOProduct> getProducts() 
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String queryString = "select * from product";
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
        
    }
    
    //search product
    public ArrayList<DTOProduct> searchProducts(String name, String manu, long priceFrom, long priceTo)
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String queryString = "select * from manufacture m, product p where p.manufacture_id = m.manufacture_id ";
            if(!"".equals(name))
                queryString += " and p.product_name like '%"+ name +"%'";
            if(!"Tất cả các hãng".equals(manu))
                queryString += " and m.manufacture_name = '"+ manu +"'";
            if(priceFrom >= 0)
                queryString += " and p.product_price >= " + priceFrom;
            if(priceTo > 0)
                queryString += " and p.product_price <= " + priceTo;
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
            
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //get product by ID
    public DTOProduct getProductbyID(int ID)
    {
        try
        {
            DTOProduct dtoPro = null;
            String queryString = "select * from product where product_id = " + ID;
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                dtoPro = new DTOProduct(rs);
            }
            return dtoPro;
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method insert new row to table product
    public boolean insertRowProduct(DTOProduct dtoPro)
    {
        try{
            DateFormat dateF = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = dateF.format(dtoPro.getProductDateUpdate());
            String queryInsert = "INSERT INTO `product`(product_name, product_price, product_quantity, product_cpu, product_ram, product_image,"
                    + " product_warranty, product_promotion, product_description, product_dateupdate, manufacture_id, provider_id) "
                    + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryInsert);
            pstmt.setString(1, dtoPro.getProductName());
            pstmt.setLong(2, dtoPro.getProductPrice());
            pstmt.setInt(3, dtoPro.getProductQuantity());
            pstmt.setString(4, dtoPro.getProductCpu());
            pstmt.setString(5, dtoPro.getProductRam());
            pstmt.setString(6, dtoPro.getProductImage());
            pstmt.setString(7, dtoPro.getProductWarranty());
            pstmt.setString(8, dtoPro.getProductPromotion());
            pstmt.setString(9, dtoPro.getProductDescription());
            pstmt.setString(10, datePro);
            pstmt.setInt(11, dtoPro.getManu().getManuID());
            pstmt.setInt(12, dtoPro.getDtoProvider().getProviderId());
            return pstmt.executeUpdate() == 1;
        }
        catch(Exception ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
    
    //method update product
    public boolean updateProduct(DTOProduct dtoPro)
    {
        try
        {
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = df.format(dtoPro.getProductDateUpdate());
            String queryUpdate;
            if(dtoPro.getProductQuantity() > 0){
                queryUpdate = String.format("update product set product_name = N'%s',"
                    + " product_price = %d,"
                    + " product_quantity = %d,"
                    + " product_warranty = N'%s',"
                    + " product_description = N'%s',"
                    + " product_promotion = N'%s',"
                    + " product_image = '%s',"
                    + " product_dateupdate = '%s',"
                    + " manufacture_id = %d where product_id = %d", dtoPro.getProductName(), dtoPro.getProductPrice(),
                    dtoPro.getProductQuantity(), dtoPro.getProductWarranty(), dtoPro.getProductDescription(), dtoPro.getProductPromotion(),
                    dtoPro.getProductImage(), datePro, dtoPro.getManu().getManuID(), dtoPro.getProductID());
            }
            else
                queryUpdate = String.format("UPDATE product SET product_name = N'%s',"
                        + " product_price = %d,"
                        + " product_cpu = N'%s', "
                        + " product_ram = N'%s',"
                        + " product_warranty = N'%s',"
                        + " product_description = N'%s',"
                        + " product_promotion = N'%s',"
                        + " product_image = '%s',"
                        + " product_dateupdate = '%s',"
                        + " manufacture_id = %d,"
                        + " provider_id = %d"
                        + " WHERE product_id = %d", dtoPro.getProductName(), dtoPro.getProductPrice(), dtoPro.getProductCpu(), dtoPro.getProductRam(),
                        dtoPro.getProductWarranty(), dtoPro.getProductDescription(), dtoPro.getProductPromotion(),
                        dtoPro.getProductImage(), datePro, dtoPro.getManu().getManuID(), dtoPro.getDtoProvider().getProviderId(), dtoPro.getProductID());
            return conn.exeUpdate(queryUpdate);
        }
        catch(Exception ex)
        {
            System.err.println(ex.getMessage());
            return false;
        }
    }
    
    //method delete product
    public boolean deleteProduct(DTOProduct dtoPro)
    {
        try{
            String queryDel = "delete from product where product_id = " + dtoPro.getProductID();
            return conn.exeUpdate(queryDel);
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method search product to import
    public ArrayList<DTOProduct> searchProductToImport(String name, String Manu, int quanFrom, int quanTo)
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String query = "select * from product, manufacture where product.manufacture_id = manufacture.manufacture_id ";
            if(!"".equals(name))
                query += " and product_name like '%" + name +"%'";
            if(!"Tất cả các hãng".equals(Manu))
                query += "and manufacture_name = '"+ Manu +"'";
            if(quanFrom >= 0 && quanTo >= 0){
                //if(quanFrom < quanTo)
                    query += "and product_quantity between " +quanFrom+ " and " + quanTo + " ";
            }
            ResultSet rs = conn.executeQuery(query);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
            
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method search product to create order
    public ArrayList<DTOProduct> searchProToCreateOrder(String name)
    {
        try{
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String querySelect = "select * from `product` where product_name like '%" + name + "%'";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.print(ex);
            return null;
        }
    }
    
    //Filter//////////////////////////////////////////////////////
    //method get CPU
    public ArrayList<String> getCPU()
    {
        try{
            ArrayList<String> list = new ArrayList<>();
            String querySelect = "SELECT DISTINCT `product_cpu` FROM `product` GROUP BY `product_cpu`";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                list.add(rs.getString("product_cpu"));
            }
            return list;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //method get RAM
    public ArrayList<String> getRAM()
    {
        try{
            ArrayList<String> list = new ArrayList<>();
            String querySelect = "SELECT DISTINCT `product_ram` FROM `product` GROUP BY `product_ram`";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                list.add(rs.getString("product_ram"));
            }
            return list;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //filter product with 3 conditions
    public ArrayList<DTOProduct> filterProduct(DTOManufacture[] manufactures, String[] CPUs, String[] RAMs)
    {
        try{
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String querySelect = "SELECT * FROM `product` p, `manufacture` m WHERE p.manufacture_id = m.manufacture_id ";
            if(manufactures.length > 0)
            {
                querySelect += " AND (";
                int count = 0;
                for (DTOManufacture manufacture : manufactures) 
                {
                    querySelect += " p.manufacture_id = " + manufacture.getManuID();
                    count++;
                    if(count == manufactures.length)
                    {
                        querySelect += " )";
                        break;
                    }
                    else
                        querySelect += " OR ";
                }
            }
            if(CPUs.length > 0)
            {
                querySelect += " AND (";
                int count = 0;
                for (String cpu : CPUs) 
                {
                    querySelect += " product_cpu = '"+cpu+"'";
                    count++;
                    if(count == CPUs.length)
                    {
                        querySelect += " ) ";
                        break;
                    }
                    else
                        querySelect += " OR ";
                }
            }
            if(RAMs.length > 0)
            {
                querySelect += " AND (";
                int count = 0;
                for (String ram : RAMs) 
                {
                    querySelect += " product_ram = '" + ram + "'";
                    count++;
                    if(count == RAMs.length)
                    {
                        querySelect += " ) ";
                        break;
                    }
                    else
                        querySelect += " OR ";
                }
            }
            querySelect += " GROUP BY product_id";
            //System.out.println(querySelect);
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //filter cpu by manufacture
    public ArrayList<DTOProduct> filterCpu(DTOManufacture[] manufactures)
    {
        try{
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String querySelect = "SELECT * FROM `product` p, `manufacture` m WHERE p.manufacture_id = m.manufacture_id ";
            if(manufactures.length > 0)
            {
                querySelect += " AND";
                int count = 0;
                for (DTOManufacture manufacture : manufactures) {
                    querySelect += " p.manufacture_id = " + manufacture.getManuID();
                    count++;
                    if(count == manufactures.length)
                        break;
                    else
                        querySelect += " OR ";
                }
            }
            querySelect += " GROUP BY product_cpu";
            //System.out.println(querySelect);
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //filter ram by manufacture
    public ArrayList<DTOProduct> filterRam(DTOManufacture[] manufactures)
    {
        try{
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String querySelect = "SELECT * FROM `product` p, `manufacture` m WHERE p.manufacture_id = m.manufacture_id ";
            if(manufactures.length > 0)
            {
                querySelect += " AND";
                int count = 0;
                for (DTOManufacture manufacture : manufactures) {
                    querySelect += " p.manufacture_id = " + manufacture.getManuID();
                    count++;
                    if(count == manufactures.length)
                        break;
                    else
                        querySelect += " OR ";
                }
            }
            querySelect += " GROUP BY product_ram";
            //System.out.println(querySelect);
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //filter product by manufacture 
    public ArrayList<DTOProduct> filterRamByCpu(DTOManufacture[] manufactures, String[] CPUs)
    {
        try{
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String querySelect = "SELECT * FROM `product` p, `manufacture` m WHERE p.manufacture_id = m.manufacture_id ";
            if(manufactures.length > 0)
            {
                querySelect += " AND (";
                int count = 0;
                for (DTOManufacture manufacture : manufactures) 
                {
                    querySelect += " p.manufacture_id = " + manufacture.getManuID();
                    count++;
                    if(count == manufactures.length)
                    {
                        querySelect += " )";
                        break;
                    }
                    else
                        querySelect += " OR ";
                }
            }
            if(CPUs.length > 0)
            {
                querySelect += " AND (";
                int count = 0;
                for (String cpu : CPUs) 
                {
                    querySelect += " product_cpu = '"+cpu+"'";
                    count++;
                    if(count == CPUs.length)
                    {
                        querySelect += " ) ";
                        break;
                    }
                    else
                        querySelect += " OR ";
                }
            }
            querySelect += " GROUP BY product_ram";
            //System.out.println(querySelect);
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
}
